package com.example.newscrolling.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import com.example.newscrolling.enity.Movie;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class favouriteDAO {
	private static final String DB_NAME = "user.db";
	private static final String TableName = "user";
	private static final String TAG = "SQLITE";
	private Context ctx;

	public favouriteDAO(Context ctx) {
		super();
		this.ctx = ctx;
	}

	// Open Database
	private void copyDatabase(File dbFile) throws IOException {
		InputStream is = ctx.getAssets().open(DB_NAME);
		OutputStream os = new FileOutputStream(dbFile);

		byte[] buffer = new byte[1024];
		while (is.read(buffer) > 0) {
			os.write(buffer);
		}

		os.flush();
		os.close();
		is.close();
	}

	public SQLiteDatabase openReadDatabase() {
		File dbFile = ctx.getDatabasePath(DB_NAME);

		if (!dbFile.exists()) {
			try {
				File parentDir = new File(dbFile.getParent());
				if (!parentDir.exists()) {
					parentDir.mkdir();
				}
				copyDatabase(dbFile);
			} catch (IOException e) {
				throw new RuntimeException("Error creating source database", e);
			}
		}

		return SQLiteDatabase.openDatabase(dbFile.getPath(), null,
				SQLiteDatabase.OPEN_READONLY);
	}

	public SQLiteDatabase openWriteDatabase() {
		File dbFile = ctx.getDatabasePath(DB_NAME);
		if (!dbFile.exists()) {
			try {
				File parentDir = new File(dbFile.getParent());
				if (!parentDir.exists()) {
					parentDir.mkdir();
				}
				copyDatabase(dbFile);
			} catch (IOException e) {
				throw new RuntimeException("Error creating source database", e);
			}
		}

		return SQLiteDatabase.openDatabase(dbFile.getPath(), null,
				SQLiteDatabase.OPEN_READWRITE);
	}

	// CRUD
	public List<Movie> getAll() {
		List<Movie> ret = new ArrayList<Movie>();
		SQLiteDatabase db = this.openReadDatabase();
		String sql = "SELECT * FROM USER;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(0);
				String name = cursor.getString(2);
				String time = cursor.getString(2);
				Movie u = new Movie(id,time, name, null);
				ret.add(u);
			} while (cursor.moveToNext());
		}
		return ret;
	}
	
	public List<Movie> delAll1() {
		List<Movie> ret = new ArrayList<Movie>();
		SQLiteDatabase db = this.openReadDatabase();
		cleanHistoryTable();
		String sql = "SELECT * FROM USER;";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(0);
				String time = cursor.getString(1);
				String name = cursor.getString(2);
				
				Movie d = new Movie(id, time, name, null);
				ret.add(d);
			} while (cursor.moveToNext());
		}
		db.close();
		return ret;
	}

	public Movie getUserById(String name) {
		SQLiteDatabase db = this.openReadDatabase();
		String sql = "SELECT * FROM USERS WHERE NAME = " + "'" + name + "'" + ";";
		Cursor cursor = db.rawQuery(sql, null);
		Movie ret = null;
		if (cursor.moveToFirst()) {
			name = cursor.getString(2);
			int id = cursor.getInt(0);
			String time = cursor.getString(1);
			ret = new Movie(id, time, name, null);
		}
		return ret;
	}
	
	public String getName(int id){
		SQLiteDatabase db = this.openReadDatabase();
		String sql = "SELECT * FROM USER";
		Cursor cursor = db.rawQuery(sql, null);
		String name = null;
		String ook = null;
		if(cursor.moveToFirst()){
			cursor.moveToPosition(id);
			name = cursor.getString(2);
			ook = cursor.getString(3);
		}
		
		return name+ook+"卡";		
	}
	
	// 刪除資料庫內容
		public List<Movie> delAll() {
			List<Movie> ret = new ArrayList<Movie>();
			cleanHistoryTable();
			SQLiteDatabase db = this.openReadDatabase();
			String sql = "SELECT * FROM USER;";
			Cursor cursor = db.rawQuery(sql, null);
			if (cursor.moveToFirst()) {
				do {
					int id = cursor.getInt(0);
					String time = cursor.getString(1);
					String name = cursor.getString(2);
					byte[] image = cursor.getBlob(3);
					Movie d = new Movie(id, time, name,null);
					ret.add(d);
				} while (cursor.moveToNext());
			}
			db.close();
			return ret;
		}

		// 清除資料庫內容
		public void cleanHistoryTable() {
			SQLiteDatabase db = this.openWriteDatabase();
			db.execSQL("DROP TABLE IF EXISTS USER");
			this.onCreate(db);
			db.close();
		}

		// 建立資料庫
		private void onCreate(SQLiteDatabase db) {
			String sql = "CREATE TABLE IF NOT EXISTS "+ TableName + "(id INTEGER PRIMARY KEY AUTOINCREMENT, NAME text , HEAT integer);";
			db.execSQL(sql);
		}

	//查詢後新增至User自訂資料庫
	public boolean insert(Movie u) {
		SQLiteDatabase db = this.openWriteDatabase();
		try {
			long uid = db.insert("USER", null, u.toContentValues());
			Log.i(TAG, "New ID = "+uid);
		} catch (Exception ex) {
			Log.e(TAG, ex.getMessage());
			return false;
		}
		db.close();
		return true;
	}
	
	public boolean update(Movie u) {
		SQLiteDatabase db = this.openWriteDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put("TIME", u.getTime());
			values.put("NAME", u.getName());
			db.update("USER", values, "ID=?", new String[] { u.getId() + "" });
		} catch (Exception ex) {
			Log.e(TAG, ex.getMessage());
			return false;
		}
		db.close();
		return true;
	}

	public boolean delete(int id) {
		SQLiteDatabase db = this.openWriteDatabase();
		try {
			db.delete("USER", "ID=?", new String[] { id + "" });
		} catch (Exception ex) {
			Log.e(TAG, ex.getMessage());
			return false;
		}
		db.close();
		return true;
	}
	
	// 取得熱量
			public int getHeat() {
				int ret = 0;
				SQLiteDatabase db = this.openReadDatabase();
				String sql = "SELECT * FROM USER;";
				Cursor cursor = db.rawQuery(sql, null);
				if (cursor.moveToFirst()) {
					do {
						int heat = cursor.getInt(3);
						ret += heat;
					} while (cursor.moveToNext());

				}
				db.close();
				return ret;
			}

}
